package cn.leida.ruoyi.system.dao;

import cn.leida.ruoyi.common.core.domain.entity.SysDept;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * 部门管理 数据层
 *
 * @author liutietou
 * @since 1.0  2020-12-11
 */
@Repository
public interface SysDeptDao extends JpaRepository<SysDept, Long>, JpaSpecificationExecutor<SysDept> {

    List<SysDept> findByParentIdAndDeptName(Long parentId, String deptName);

    @Query(value = " select count(1) from sys_dept where status = 0 and del_flag = '0' and find_in_set(?1, ancestors) ", nativeQuery = true)
    Integer findNormalChildrenDeptById(Long deptId);

    @Query(value = " select * from sys_dept where  status = 0 and del_flag = '0' and ( ancestors = ?1 or ancestors like '%,?2,%' or ancestors like '?3,%' or ancestors like '%?4,' )", nativeQuery = true)
    Integer findSqliteNormalChildrenDeptById(Long deptId1, Long deptId2, Long deptId3, Long deptId4);

    @Query(value = " select count(1) from sys_dept where del_flag = '0' and parent_id=?1 limit 1 ", nativeQuery = true)
    Integer hasChildByDeptId(Long deptId);

    @Query(value = " select count(1) from sys_user where dept_id = ?1 and del_flag = '0' ", nativeQuery = true)
    Integer checkDeptExistUser(Long deptId);

    @Query(value = " select * from sys_dept where find_in_set(?1, ancestors) ", nativeQuery = true)
    List<SysDept> findChildrenDeptById(Long deptId);

    @Query(value = " select * from sys_dept where ancestors = ?1 or ancestors like '%,?2,%' or ancestors like '?3,%' or ancestors like '%?4,' ", nativeQuery = true)
    List<SysDept> findSqliteChildrenDeptById(Long deptId1, Long deptId2, Long deptId3, Long deptId4);


    @Query(value = """
        select  d.deptId from SysDept d left join SysRoleDept rd on d.deptId = rd.deptId  
        where rd.roleId = ?1  order by d.parentId desc, d.orderNum desc
        """, nativeQuery = true)
    public List<Long> findDeptListByRoleId(Long roleId);

    @Query(value = """
        select  d.deptId from SysDept d left join SysRoleDept rd on d.deptId = rd.deptId  
        where rd.roleId = ?1   and d.deptId not in (select d.parentId from SysDept d inner join SysRoleDept rd on d.deptId = rd.deptId and rd.roleId = ?1
        order by d.parentId desc, d.orderNum desc
        """, nativeQuery = true)
    public List<Long> findDeptCheckListByRoleId(Long roleId);

}
